#!/usr/bin/python

import pymysql
import re


def db_connect(db_param):
    if db_param.get('SERVER') is None:
        db_param['SERVER'] = 'localhost'
    if db_param.get('PORT') is None:
        db_param['PORT'] = 3306
    if db_param.get('CHARSET') is None:
        db_param['CHARSET'] = 'utf8'
    # cursorclass = pymysql.cursors.DictCursor  cursor结果集是字典
    # cursorclass = pymysql.cursors.Cursor      cursor结果集是元组
    connection = pymysql.connect(
        host=db_param['SERVER'],
        port=db_param['PORT'],
        user=db_param['USER'],
        password=db_param['PWD'],
        db=db_param['DBNAME'],
        charset=db_param['CHARSET'],
        autocommit=True,
        cursorclass=pymysql.cursors.Cursor
    )
    return connection


def db_disconnect(connection):
    if connection is not None:
        connection.close()
        connection = None
    return connection


# params, arrayParams  只有一个参数时，需要加逗号  (param1,)  用来消除 括号()既可以表示tuple，又可以表示数学公式中的小括号
# params 普通参数
# arrayParams   in, not in 列表参数，采用拼接实现
def fetch_multi_row(connection, sql, params=(), arrayParams=(), cursorclass=pymysql.cursors.Cursor):
    if len(arrayParams) > 0:
        sql = sqlArrayParamSplice(sql, arrayParams)
    with connection.cursor(cursor=cursorclass) as cursor:
        cursor.execute(sql, params)
        result_set = cursor.fetchall()
        row_count = cursor.rowcount
        column_name = GetColumnNameFromCorsor(cursor)
    return result_set, row_count, column_name


def fetch_single_row(connection, sql, params=(), arrayParams=(), cursorclass=pymysql.cursors.Cursor):
    if len(arrayParams) > 0:
        sql = sqlArrayParamSplice(sql, arrayParams)
    with connection.cursor(cursor=cursorclass) as cursor:
        cursor.execute(sql, params)
        result_set = cursor.fetchone()
        row_count = cursor.rowcount
        column_name = GetColumnNameFromCorsor(cursor)
    return result_set, row_count, column_name


def fetch_multi_dict(connection, sql, params=(), arrayParams=()):
    return fetch_multi_row(connection, sql, params, arrayParams, pymysql.cursors.DictCursor)


def fetch_single_dict(connection, sql, params=(), arrayParams=()):
    return fetch_single_row(connection, sql, params, arrayParams, pymysql.cursors.DictCursor)


def GetColumnNameFromCorsor(cursor):
    column = []
    for field in cursor.description:
        column.append(field[0])
    return column


def sqlArrayParamSplice(srcSql, arrayParams):
    arrayParams = list(arrayParams)  # tuple 没有pop
    if len(arrayParams) == 0:
        return srcSql

    # 匹配第一个 .*?in(%s)   正则默认贪婪，  .*?  加?号匹配模式变为非贪婪
    # (?i)^   在正则表达式最前面加上(?i)可以忽略后面所有的大小写
    # sql要替换掉换行符，多行匹配不成功
    srcSql = srcSql.replace("\n", " ")
    m = re.match('(?i)^(.*?\sin\s*\(\s*)\%s(\s*\).*)', srcSql)
    if not m:  # 未匹配
        print("ERROR, sqlArrayParamSplice no match, need check sql")
        print(srcSql)
        return srcSql

    strHead = m.group(1)
    strTail = m.group(2)
    sqlParamIn = ','.join(str(x) for x in arrayParams[0])
    # 拼接SQL, 将in内容拼接到SQL中
    srcSql = strHead + sqlParamIn + strTail

    arrayParams.pop(0)
    return sqlArrayParamSplice(srcSql, arrayParams)
